package cn.com.surker.sale.service.impl;

import cn.com.surker.common.Code;
import cn.com.surker.database.JDBCTemplateCache;
import cn.com.surker.sale.dao.SaleClientDao;
import cn.com.surker.sale.dao.SaleClientOrgDao;
import cn.com.surker.sale.entity.SaleClient;
import cn.com.surker.sale.entity.SaleClientOrg;
import cn.com.surker.sale.service.SaleClientBaseService;
import cn.com.surker.system.dao.DataSourceInfoDao;
import cn.com.surker.system.dao.SysOriginDao;
import cn.com.surker.system.entity.DataSourceInfo;
import cn.com.surker.system.entity.SysOrigin;
import cn.com.surker.util.DateUtil;
import cn.com.surker.util.StringUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author lucky
 * @create 2024年07月10 9:13
 * @desc
 */
@Slf4j
public abstract class SaleClientBaseServiceImpl extends ServiceImpl<SaleClientDao, SaleClient> implements SaleClientBaseService {

    @Resource
    protected SaleClientDao saleClientDao;
    @Resource
    protected SaleClientOrgDao saleClientOrgDao;
    @Resource
    protected DataSourceInfoDao dsDao;
    @Resource
    protected SysOriginDao sysOriginDao;

    /**
     * 分页查询
     *
     * @param status   状态
     * @param offset   分页参数,第几页
     * @param limit    分页参数,每页数量
     * @return 返回分页数据
     */
    public IPage<SaleClient> queryPage(String searchVal, Integer status, Integer offset, Integer limit) {
        PageHelper.offsetPage(offset, limit);
        QueryWrapper<SaleClient> query = new QueryWrapper<>();
        if (StringUtils.isNull(status))
            status = 0;
        if (!StringUtils.isNull(status))
            query.eq("status", status);
        if (!StringUtils.isNull(searchVal))
            query.and(orderWrapper -> orderWrapper.like("name", searchVal).or()
                    .like("code", searchVal).or().like("full_name", searchVal)
                    .or().like("tax_code", searchVal).or().like("link_man", searchVal)
                    .or().like("phone", searchVal).or().like("remark", searchVal));
        query.orderByDesc("create_time");

        IPage<SaleClient> iPage = saleClientDao.selectPage(new Page<>((offset + limit) /limit, limit), query);
        /**分页插件**/
        if (!StringUtils.isNull(iPage.getRecords())) {
            PageInfo pageInfo = new PageInfo(iPage.getRecords());
            if (!StringUtils.isNull(pageInfo.getTotal()))
                iPage.setTotal(pageInfo.getTotal());
            List<SaleClient> list = iPage.getRecords();
            List<SaleClient> resultList = new ArrayList<>();
            for (Integer i = 0; i < list.size(); i++) {
                SaleClient client = list.get(i);
                /**查询所属部门**/
                List<SaleClientOrg> orgList = saleClientOrgDao.linkOrgs(client.getId());
                if (orgList != null && orgList.size() > 0) {
                    Boolean slat = true;
                    for (SaleClientOrg org : orgList) {
                        if (org.getLink() == 1) {
                            if (slat) {
                                slat = false;
                                client.setOrgName(org.getOrgName() + ",");
                            } else {
                                client.setOrgName(client.getOrgName() + org.getOrgName() + ",");
                            }
                        }
                    }
                    if (!StringUtils.isNull(client.getOrgName()))
                        client.setOrgName(client.getOrgName().substring(0, client.getOrgName().length() - 1));
                }
                client.setOrgList(orgList);
                resultList.add(client);
            }
            iPage.setRecords(resultList);
        }
        return iPage;
    }

    /**
     * 保存或返回erp客户id
     * @param clientId
     * @return
     */
    @Override
    public Integer saveErp(Long clientId, Long orgId) {
        if (StringUtils.isAllParamNull(clientId, orgId))
            return Code.PARAM_EMPTY.getCode();
		SaleClientOrg saleClientOrg = saleClientOrgDao.findByOrg(clientId, orgId, 0);
        if (saleClientOrg != null && !StringUtils.isNull(saleClientOrg) && !
                StringUtils.isNull(saleClientOrg.getErpId()) && saleClientOrg.getErpId() > 0) {
            return saleClientOrg.getErpId().intValue();
        }
        SaleClient client = saleClientDao.selectById(clientId);
        if (client == null)
            return -1;

        if (StringUtils.isNull(client.getFullName()))
        /**请填写客户信息**/
            return Code.CLIENT_NAME_EMPTY.getCode();
        if (StringUtils.isNull(client.getTaxCode()))
        /**请填写客户信息**/
            return Code.GROUP_TAXCODE_EMPTY.getCode();
        Long taskId = -1l;
        DataSourceInfo ds = dsDao.queryDSByOriginId(orgId);
        if (ds == null || ds.getStatus() == 1)
            return Code.STATION_ERROR.getCode();
        if (ds.getType() != 0) {
            /**获取数据库联系信息**/
            JdbcTemplate jdbcTemplate = JDBCTemplateCache.getLocalJdbcTemplate(ds);
            if (!StringUtils.isNull(client.getTaxCode())) {
                String sql = "select top 1 * from Market_Client_T where taxNo = '" + client.getTaxCode() + "'";
                List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
                if (list != null && list.size() > 0) {
                    String updateSql = "update Market_Client_T set u_name ='" + client.getFullName() + "',isValid = 1 where taxNo = '" + client.getTaxCode() + "'";
                    Integer code = jdbcTemplate.update(updateSql);
                    if (code > 0) {
                        SysOrigin origin = sysOriginDao.selectById(orgId);
                        saveClientOrg(clientId, client.getType(), StringUtils.convertLong(list.get(0).get("ID")), orgId, ds.getId(), origin.getOriginName());
                        return StringUtils.convertInteger(list.get(0).get("ID"));
                    }  else {

                        return -1;
                    }
                }
            }
            StringBuffer addSql = new StringBuffer();
            addSql.append("insert into Market_Client_T (u_name,u_code,rating,creditControls,CreationTime,ReviseTime,taxNo) values(?,?,?,?,?,?,?)");
            String nowTime = DateUtil.convertString(new Date(), "yyyy-MM-dd HH:mm:ss");
            Object[] params = {client.getFullName(), "", 0, 1, nowTime, nowTime, client.getTaxCode()};
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(addSql.toString(), new String[]{"ID"});
                    for (int i = 0; i < params.length; i++) {
                        ps.setObject(i + 1, params[i]);
                    }
                    return ps;
                }
            }, keyHolder);
            taskId = keyHolder.getKey().longValue();
            log.info("saveClient:新建客户信息SQL:{},参数:{}", addSql.toString(), params);
            if (taskId < 1)
                return -1;
        }
        SysOrigin origin = sysOriginDao.selectById(orgId);
        Integer code = saveClientOrg(clientId, client.getType(), taskId, orgId, ds.getId(), origin.getOriginName());
        if (code == 0) {
            if (ds.getType() == 0)
                return clientId.intValue();
            else
                return taskId.intValue();
        }
        return -1;
    }

    private Integer saveClientOrg(Long clientId, Integer type, Long erpId, Long orgId, Long dsId, String orgName) {
        SaleClientOrg org = saleClientOrgDao.findByOrg(clientId, orgId, 0);
        if (org != null) {
            org.setErpId(erpId);
            org.setDsId(dsId);
            org.setUpdateTime(new Date());
            Integer code = saleClientOrgDao.updateById(org);
            if (code > 0)
                return 0;
            else
                return -1;
        } else {
            org = new SaleClientOrg();
            org.setStatus(0);
            org.setErpId(erpId);
            org.setClientId(clientId);
            org.setCreateTime(new Date());
            org.setDsId(dsId);
            org.setOrgId(orgId);
            org.setOrgName(orgName);
            Integer code = saleClientOrgDao.insert(org);
            if (code > 0)
                return 0;
            else
                return -1;
        }
    }

    /**
     *
     * @param orgId
     * @return
     */
    public List<SaleClient> queryByOrg(Long orgId) {
        QueryWrapper<SaleClient> query = new QueryWrapper<>();
        query.eq("status", 0);
        if (!StringUtils.isNull(orgId)) {
            query.inSql("id", "select client_id from sale_client_org where status = 0 and org_id = " + orgId);
        }
        query.orderByDesc("create_time");
        List<SaleClient> list = saleClientDao.selectList(query);
        if (list == null || list.size() == 0)
            return null;
        return list;
    }

	public SaleClient queryByTaxCode(String taxCode) {
		QueryWrapper<SaleClient> qw = new QueryWrapper<>();
		qw.eq("tax_code", taxCode);
		List<SaleClient> saleClients = saleClientDao.selectList(qw);
		if (saleClients != null && saleClients.size() > 0) {
			SaleClient saleClient = saleClients.get(0);
			return saleClient;
		}
		return null;
	}

	public SaleClient queryByName(String name) {
		QueryWrapper<SaleClient> qw = new QueryWrapper<>();
		qw.eq("name", name);
		List<SaleClient> saleClients = saleClientDao.selectList(qw);
		if (saleClients != null && saleClients.size() > 0) {
			SaleClient saleClient = saleClients.get(0);
			return saleClient;
		}
		return null;
	}

	public SaleClient queryClient(String name, String fullName, String taxCode, String linkMan, Integer status) {

		List<SaleClient> saleClients = queryList(name, fullName, taxCode, linkMan, null, status);
		if (saleClients != null && saleClients.size() > 0) {
			saleClients.get(0);
		}
		return null;
	}

	public List<SaleClient> queryList(String name, String fullName, String taxCode, String linkMan, Long orgId, Integer status) {
		QueryWrapper<SaleClient> qw = new QueryWrapper<>();
		if (StringUtils.isNotBlank(name)) {
			qw.like("name", name);
		}
		if (StringUtils.isNotBlank(fullName)) {
			qw.like("full_name", fullName);
		}
		if (StringUtils.isNotBlank(taxCode)) {
			qw.like("tax_code", taxCode);
		}
		if (StringUtils.isNotBlank(linkMan)) {
			qw.like("link_name", linkMan);
		}
		if (orgId != null) {
			qw.inSql("id", "select client_id from sale_client_org where status = 0 and org_id = " + orgId);
		}
		if (status != null) {
			qw.eq("status", status);
		}
		List<SaleClient> saleClients = saleClientDao.selectList(qw);
		return saleClients;
	}

	public IPage<SaleClient> queryPage(String name, String fullName, String taxCode, String linkMan, Integer status, Integer offset, Integer limit) {
		QueryWrapper<SaleClient> qw = new QueryWrapper<>();
		if (StringUtils.isNotBlank(name)) {
			qw.like("name", name);
		}
		if (StringUtils.isNotBlank(fullName)) {
			qw.like("full_name", fullName);
		}
		if (StringUtils.isNotBlank(taxCode)) {
			qw.like("tax_code", taxCode);
		}
		if (StringUtils.isNotBlank(linkMan)) {
			qw.like("link_man", linkMan);
		}
		if (status != null) {
			qw.eq("status", status);
		}
		qw.orderByDesc("create_time");
		PageHelper.offsetPage(offset, limit);
		IPage<SaleClient> iPage = saleClientDao.selectPage(new Page<>((offset + limit) / limit, limit), qw);
		/**分页插件**/
		if (!StringUtils.isNull(iPage.getRecords())) {
			PageInfo pageInfo = new PageInfo(iPage.getRecords());
			if (!StringUtils.isNull(pageInfo.getTotal()))
				iPage.setTotal(pageInfo.getTotal());
		}
		return iPage;
	}
}
